﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using XCommon.Types;
using MySql.Data.MySqlClient;

namespace XDAL
{
    public class tbPhotos : tbBase<tbPhotos>
    {
        // 按发布日期,权重,评分排序
        private const string SQL_SELECTTODAYLIST = @"
SELECT h.itemid,h.title,h.date,h.imageurl,h.des,h.groupid,h.vindex,h.vpower ,case when t.count is null then 0 else t.count end  count,case when t.score is null then 0 else t.score end  score
FROM tbphotohead as  h left join 
(select count(0) AS count,avg(score) AS score,itemid
from tbassess group by itemid) as t
ON  t.itemid = h.itemid 
ORDER BY h.date DESC,h.vindex DESC,score DESC
LIMIT @pagebegin,@pageend
";
        private const string SQL_SELECTGROUPLIST = @"
SELECT h.itemid,h.title,h.date,h.imageurl,h.des,h.groupid,h.vindex,h.vpower ,case when t.count is null then 0 else t.count end  count,case when t.score is null then 0 else t.score end  score
FROM tbphotohead as  h left join 
(select count(0) AS count,avg(score) AS score,itemid
from tbassess group by itemid) as t
ON  t.itemid = h.itemid 
WHERE groupid =@groupid 
ORDER BY h.date DESC,h.vindex DESC,score DESC
LIMIT @pagebegin,@pageend
";

        private const string SQL_SELECT_PHOTODETAIL = @"
SELECT d.* FROM tbphotodetail as d  WHERE d.itemid = @itemid;
";
        private const string SQL_SELECT_ASSESS = @"
SELECT a.* FROM tbassess as a WHERE a.itemid = @itemid;
";

        private const int S_PAGECOUNT = 20;
        public List<Ps> SelectToDayList(int pagebegin)
        {
            MySqlParameter[] xasa = new MySqlParameter[] {
                new MySqlParameter("@pagebegin",pagebegin),
                new MySqlParameter("@pageend",pagebegin+S_PAGECOUNT)
            };
            return MySQL.MySqlHelper.ReadToList(MySQL.MySqlHelper.ExecuteReader(SQL_SELECTTODAYLIST, xasa));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="igroupid">分类ID</param>
        /// <param name="pageindex">页码 默认值 = 0</param>
        /// <returns></returns>
        public List<Ps> SelectGroupList(int igroupid, int pageindex)
        {
            int pagebegin = pageindex <= 0 ? 0 : pageindex * S_PAGECOUNT;

            if (igroupid == 0)
            {
                return SelectToDayList(pagebegin);
            }

            MySqlParameter[] xasa = new MySqlParameter[] {
                new MySqlParameter("@groupid",igroupid),
                new MySqlParameter("@pagebegin",pagebegin),
                new MySqlParameter("@pageend",pagebegin+S_PAGECOUNT)
            };
            return MySQL.MySqlHelper.ReadToList(MySQL.MySqlHelper.ExecuteReader(SQL_SELECTGROUPLIST, xasa));
        }

        public List<Ps>[] SelectPhotoItemByID(ushort itemid)
        {
            MySqlParameter[] xasa = new MySqlParameter[] {
                new MySqlParameter("@itemid",itemid)
            };

            List<Ps>[] photoDetail = MySQL.MySqlHelper.ReadToLists(MySQL.MySqlHelper.ExecuteReader(SQL_SELECT_PHOTODETAIL + SQL_SELECT_ASSESS, xasa));

            return photoDetail;
        }

    }
}
